﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace DAL
{
    public class KhamBenhDAL
    {
        PhongMachTuDBDataContext db;
        public IQueryable LayDSKhamBenh()
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    select new { 
                    s.BENHNHAN.HoTen,
                    s.BENHNHAN.DiaChi,
                    GioiTinh=s.BENHNHAN.GioiTinh?"Nam":"Nữ",
                    s.BENHNHAN.NgaySinh,
                    s.MaKhamBenh,
                    s.MaBenhNhan,
                    };
            return q;
        }
        public IQueryable LayDSKhamBenh(DateTime dt)
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    where s.NgayKham.Date == dt
                    select new
                    {
                        s.BENHNHAN.HoTen,
                        s.BENHNHAN.DiaChi,
                        GioiTinh = s.BENHNHAN.GioiTinh ? "Nam" : "Nữ",
                        s.BENHNHAN.NgaySinh,
                        s.MaKhamBenh,
                        s.MaBenhNhan,
                    };
            return q;
        }
        public List<KHAMBENH> LayDSKhamBenh(int maKB,int maBN)
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    where s.MaBenhNhan==maBN && s.MaKhamBenh==maKB
                    select s;
            return q.ToList();
        }
        public IQueryable timKiemDS(string dt)
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    where s.BENHNHAN.HoTen.Contains(dt) 
                    select new
                    {
                        s.BENHNHAN.HoTen,
                        s.BENHNHAN.DiaChi,
                        GioiTinh = s.BENHNHAN.GioiTinh ? "Nam" : "Nữ",
                        s.BENHNHAN.NgaySinh,
                        s.MaKhamBenh,
                        s.MaBenhNhan,

                    };
            return q;
        }
        public IQueryable timKiemDS(int dt)
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    where s.MaBenhNhan == dt || s.MaKhamBenh == dt
                    select new
                    {
                        s.BENHNHAN.HoTen,
                        s.BENHNHAN.DiaChi,
                        GioiTinh = s.BENHNHAN.GioiTinh ? "Nam" : "Nữ",
                        s.BENHNHAN.NgaySinh,
                        s.MaKhamBenh,
                        s.MaBenhNhan,
                    };
            return q;
        }
        public IQueryable LayDSKhamBenh(int month)
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    where s.NgayKham.Date.Month == month
                    select new
                    {
                        s.BENHNHAN.HoTen,
                        s.BENHNHAN.DiaChi,
                        GioiTinh = s.BENHNHAN.GioiTinh ? "Nam" : "Nữ",
                        s.BENHNHAN.NgaySinh,
                        s.MaKhamBenh,
                        s.MaBenhNhan,
                    };
            return q;
        }
        public IQueryable LayDSKhamBenh(int day,int month,int year)
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    where s.NgayKham.Date.Day == day && s.NgayKham.Date.Month == month && s.NgayKham.Date.Year == year
                    select new
                    {
                        s.MaKhamBenh,
                    };

            return q;
        }
        public int thongKeTheoNgay(int day, int mont, int year)
        {
            db = new PhongMachTuDBDataContext();
            var q = (from s in db.KHAMBENHs
                    where s.NgayKham.Date.Day==day && s.NgayKham.Date.Month==mont && s.NgayKham.Date.Year==year
                    select s).Count();

            return q;
        }
        //public DataTable thongKeTheoNgayFULL(int day, int mont, int year)
        //{
        //    db = new PhongMachTuDBDataContext();
        //    int Month = DateTime.Now.Month;
        //    int Year = DateTime.Now.Year;
        //    for ( day = 1; day < DateTime.DaysInMonth(Year, Month); day++)
        //    {
        //        int stt = 0;
        //        DataTable table = new DataTable();
        //        table.Columns.Add("STT", typeof(int));
        //        table.Columns.Add("Ngày", typeof(int));
        //        table.Columns.Add("Số Bệnh Nhân", typeof(int));
        //        table.Columns.Add("Doanh Thu", typeof(int));
        //        table.Columns.Add("Tỉ Lệ", typeof(int));


        //    }
        //    return q.ToList();
        //}
        public int demBenhNhan1Ngay(DateTime dt)
        {
            db = new PhongMachTuDBDataContext();
            var q = (from s in db.KHAMBENHs
                     where s.NgayKham.Date == dt
                     select s).Count();

            return q;
        }



        public bool ThemKhamBenh(KHAMBENH s)
        {

             
                try
                {
                    db = new PhongMachTuDBDataContext();
                    KHAMBENH r = new KHAMBENH();
                    r.MaBenhNhan = s.MaBenhNhan;
                    r.MaLoaiBenh = s.MaLoaiBenh;
                    r.NgayKham = s.NgayKham;
    
                    r.TrieuChung = s.TrieuChung;

                    db.KHAMBENHs.InsertOnSubmit(r);
                    db.SubmitChanges();
                    return true;

                }
                catch (Exception)
                {

                    return false;
                }
        }
        public bool suaKhamBenh(KHAMBENH s)
        {

            try
            {
                db = new PhongMachTuDBDataContext();
                KHAMBENH r = db.KHAMBENHs.Single(id => id.MaKhamBenh == s.MaKhamBenh);
                r.MaBenhNhan = s.MaBenhNhan;
                r.MaLoaiBenh = s.MaLoaiBenh;
                r.TrieuChung = s.TrieuChung;

                db.SubmitChanges();
                return true;

            }
            catch (Exception)
            {

                return false;
            }
        }
        public bool xoaKhamBenh(int MaKhamBenh)
        {
            try
            {
                db = new PhongMachTuDBDataContext();
                var delete = from d in db.KHAMBENHs
                             where d.MaKhamBenh == MaKhamBenh
                             select d;
                if (delete.Count() > 0)
                {
                    db.KHAMBENHs.DeleteOnSubmit(delete.FirstOrDefault());
                    db.SubmitChanges();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public object ThongKeThuoc(int month, int year)
        {
            db = new PhongMachTuDBDataContext();       
            var q = (from c in db.CHITIETKHAMBENHs
                    where
                      c.KHAMBENH.NgayKham.Date.Month == month && c.KHAMBENH.NgayKham.Date.Year == year
                    group c by new
                    {
                        c.MaDonVi,
                        c.MaThuoc,
                        c.THUOC.TenThuoc,
                        c.DONVI.TenDonVi,

                    } into g
                    select new
                    {

                        g.Key.TenThuoc,
                        g.Key.TenDonVi,
                        SoLuong = (int?)g.Sum(p => p.SoLuong)
                    }).AsEnumerable()
                                .Select((r, i) => new
                                {
                                    STT = Convert.ToInt32(i++) + 1,
                                    Thuoc=r.TenThuoc,
                                    DonViTinh=r.TenDonVi,
                                    SoLuong=r.SoLuong,


         
                                }).ToList(); ;
            return q;
        }
        public IQueryable BaoCaoSuDungThuoc(int maBN)
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    where s.MaBenhNhan == maBN
                    select new
                    {
                        s.BENHNHAN.HoTen,
                        s.MaBenhNhan,
                        s.NgayKham,
                        s.TrieuChung,
                        s.LOAIBENH.TenLoaiBenh,

                    };
            return q;
        }
        public List<KHAMBENH> LayDSBaoCaoPKB(int maBN)
        {
            db = new PhongMachTuDBDataContext();
            var q = from s in db.KHAMBENHs
                    where s.MaBenhNhan == maBN
                    select s;
            return q.ToList();
        }
    }
}
